package dhu.cst.jnlwt.jiangnan181310209liwanting181310302.tally.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.graphics.drawable.BitmapDrawable;
import android.graphics.drawable.Drawable;

import java.io.ByteArrayOutputStream;
import java.io.IOException;

import dhu.cst.jnlwt.jiangnan181310209liwanting181310302.tally.R;
import dhu.cst.jnlwt.jiangnan181310209liwanting181310302.tally.activity_login.MD5Utils;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "tally.db";
    private static final int DB_VERSION = 1;
    //构造函数
    public DatabaseHelper(Context context) {
        super(context,context.getExternalFilesDir(null).getAbsolutePath()+"/"+DB_NAME,null,DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建用户表和类别表
        db.execSQL("CREATE TABLE IF NOT EXISTS users (_id INTEGER PRIMARY KEY AUTOINCREMENT,phone VARCHAR(11) UNIQUE,username VARCHAR(20) UNIQUE,password VARCHAR(50),avatar INTEGER)");
        db.execSQL("CREATE TABLE IF NOT EXISTS categories (_id INTEGER PRIMARY KEY AUTOINCREMENT,category VARCHAR(20) UNIQUE, type INTEGER,belong VARCHAR(20),icon INTEGER)");
        //初始化用户表和类别表
        initUsers(db);
        initCategories(db);
    }
    //数据库更新
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    //类别表初始化
    private void initCategories(SQLiteDatabase db){
        addCategory(db,"打车",1,"出行", R.drawable.dache);
        addCategory(db,"地铁",1,"出行",R.drawable.ditie);
        addCategory(db,"飞机",1,"出行",R.drawable.feiji);
        addCategory(db,"公交车",1,"出行",R.drawable.gongjiaoche);
        addCategory(db,"加油",1,"出行",R.drawable.jiayou);
        addCategory(db,"其他",1,"出行",R.drawable.qitachuxing);
        addCategory(db,"停车",1,"出行",R.drawable.tingche);
        addCategory(db,"养车修车",1,"出行",R.drawable.yangchexiuche);

        addCategory(db,"包包",1,"购物",R.drawable.baobao);
        addCategory(db,"护肤彩妆",1,"购物",R.drawable.hufucaizhuang);
        addCategory(db,"美容美甲",1,"购物",R.drawable.meirongmeijia);
        addCategory(db,"日用品",1,"购物",R.drawable.riyongpin);
        addCategory(db,"饰品",1,"购物",R.drawable.shipin);
        addCategory(db,"鞋子",1,"购物",R.drawable.xiezi);
        addCategory(db,"衣服",1,"购物",R.drawable.yifu);
        addCategory(db,"购物",1,"购物",R.drawable.gouwu);

        addCategory(db,"教育",1,"其他",R.drawable.jiaoyu);
        addCategory(db,"人情",1,"其他",R.drawable.renqing);
        addCategory(db,"手续费",1,"其他",R.drawable.shouxufei);
        addCategory(db,"投资",1,"其他",R.drawable.touzi);
        addCategory(db,"医疗",1,"其他",R.drawable.yiliao);
        addCategory(db,"意外",1,"其他",R.drawable.yiwai);
        addCategory(db,"其他",1,"其他",R.drawable.qita);

        addCategory(db,"酒水",1,"食物",R.drawable.jiushui);
        addCategory(db,"零食",1,"食物",R.drawable.lingshi);
        addCategory(db,"买菜",1,"食物",R.drawable.maicai);
        addCategory(db,"水果",1,"食物",R.drawable.shuiguo);
        addCategory(db,"晚餐",1,"食物",R.drawable.wancan);
        addCategory(db,"午餐",1,"食物", R.drawable.wucan);
        addCategory(db,"香烟",1,"食物",R.drawable.xiangyan);
        addCategory(db,"饮料",1,"食物",R.drawable.yinliao);
        addCategory(db,"早餐",1,"食物",R.drawable.zaocan);

        addCategory(db,"报销",0,"收入",R.drawable.baoxiao);
        addCategory(db,"二手闲置",0,"收入",R.drawable.ershouxianzhi);
        addCategory(db,"工资",0,"收入",R.drawable.gongzi);
        addCategory(db,"红包",0,"收入",R.drawable.hongbao);
        addCategory(db,"兼职",0,"收入",R.drawable.jianzhi);
        addCategory(db,"借钱",0,"收入",R.drawable.jieqian);
        addCategory(db,"生活费",0,"收入",R.drawable.shenghuofei);
        addCategory(db,"投资",0,"收入",R.drawable.touzi);

        addCategory(db,"电影",1,"娱乐",R.drawable.dianying);
        addCategory(db,"酒吧",1,"娱乐",R.drawable.jiuba);
        addCategory(db,"KTV",1,"娱乐",R.drawable.ktv);
        addCategory(db,"旅游",1,"娱乐",R.drawable.lvyou);
        addCategory(db,"洗浴",1,"娱乐",R.drawable.xiyu);
        addCategory(db,"游戏",1,"娱乐",R.drawable.youxi);
        addCategory(db,"娱乐",1,"娱乐",R.drawable.yule);
        addCategory(db,"运动",1,"娱乐",R.drawable.yundong);
        addCategory(db,"追星",1,"娱乐",R.drawable.zhuixing);

        addCategory(db,"电费",1,"住房",R.drawable.dianfei);
        addCategory(db,"房贷",1,"住房",R.drawable.fangdai);
        addCategory(db,"房租",1,"住房",R.drawable.fangzu);
        addCategory(db,"话费",1,"住房",R.drawable.huafei);
        addCategory(db,"家具",1,"住房",R.drawable.jiaju);
        addCategory(db,"燃气费",1,"住房",R.drawable.ranqifei);
        addCategory(db,"水费",1,"住房",R.drawable.shuifei);
        addCategory(db,"网费",1,"住房",R.drawable.wangfei);
        addCategory(db,"物业",1,"住房",R.drawable.wuye);
    }
    //用户表初始化
    private void initUsers(SQLiteDatabase db){
        addUser(db,"zhangkai", MD5Utils.md5Password("zk123456"),"15766328279");
        addUser(db,"jiangguoxu",MD5Utils.md5Password("jgx123456"),"13896051308");
        addUser(db,"huangyongmei",MD5Utils.md5Password("hym123456"),"15826168312");
        addUser(db,"liwanting",MD5Utils.md5Password("lwt123456"),"17878576039");
    }

    //向类别表中添加一条类别
    private void addCategory(SQLiteDatabase db,String category,int type,String belong,int icon){
        ContentValues values = new ContentValues();
        values.put("category",category);
        values.put("type",type);
        values.put("belong",belong);
        values.put("icon",icon);
        db.insert("categories",null,values);
    }
    //向用户表中添加一个用户，并创建改用的收入表和支出表
    public void addUser(SQLiteDatabase db,String username,String password,String phone){
        ContentValues values = new ContentValues();
        values.put("username",username);
        values.put("password",password);
        values.put("phone",phone);
        values.put("avatar",R.drawable.head_portrait);
        db.insert("users",null,values);
        //创建该用户的收入表和支出表
        Cursor cursor = db.query("users",new String[]{"_id"},"phone=?",new String[] {phone},null,null,null);
        cursor.moveToFirst();
        db.execSQL("CREATE TABLE IF NOT EXISTS income"+cursor.getString(0)+" (_id INTEGER PRIMARY KEY AUTOINCREMENT,account DOUBLE,category VARCHR(20),date VARCHAR(20),postscript VARCHAR(50))");
        db.execSQL("CREATE TABLE IF NOT EXISTS expenditure"+cursor.getString(0)+" (_id INTEGER PRIMARY KEY AUTOINCREMENT,account DOUBLE,category VARCHR(20),date VARCHAR(20),postscript VARCHAR(50))");
        cursor.close();
    }

    //修改密码
    public void changePassword(SQLiteDatabase db,String phoneNumber,String password){
        String sql = "UPDATE users SET password='"+password+"' WHERE phone='"+phoneNumber+"'";
        db.execSQL(sql);
    }
    //修改用户名
    public void changeUsername(SQLiteDatabase db,String phoneNumber,String username){
        String sql = "UPDATE users SET username='"+username+"' WHERE phone='"+phoneNumber+"'";
        db.execSQL(sql);
    }
    //获取用户名
    public String getUsername(SQLiteDatabase db,String phoneNumber){
        String sql = "SELECT username FROM users WHERE phone='"+phoneNumber+"'";
        Cursor cursor = db.rawQuery(sql,null);
        cursor.moveToFirst();
        String username = cursor.getString(cursor.getColumnIndex("username"));
        cursor.close();
        return username;
    }
    //获取密码
    public String getPassword(SQLiteDatabase db,String phoneNumber){
        String sql = "SELECT password FROM users WHERE phone='"+phoneNumber+"'";
        Cursor cursor = db.rawQuery(sql,null);
        cursor.moveToFirst();
        String password = cursor.getString(cursor.getColumnIndex("password"));
        cursor.close();
        return password;
    }
    //用户名是否已注册
    public boolean isRegistered(SQLiteDatabase db,String username){
        Cursor cursor = db.query("users", new String[]{"username"},"username=?",new String[]{username},null,null,null);
        int count = cursor.getCount();
        cursor.close();
        if(count==0){
            return false;
        }
        return true;
    }
}
